package com.ssy.dao;

import com.ssy.common.PageInfo;
import com.ssy.util.JDBCUtil;

import java.lang.reflect.Field;
import java.sql.*;
import java.util.ArrayList;
import java.util.List;

/**
 * BaseDao
 *
 * @author ssy
 * @date 2020/11/11
 **/
public class BaseDao {

    /**
     * 用于执行增删改
     *
     * @param sql
     * @param param
     * @return
     */
    public boolean update(String sql, Object... param) {
        Connection conn = JDBCUtil.getConnection();
        PreparedStatement ps = null;
        try {
            ps = conn.prepareStatement(sql);
            for (int i = 0; i < param.length; i++) {
                ps.setObject(i + 1, param[i]);
            }
            return ps.executeUpdate() == 1 ? true : false;
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            JDBCUtil.closeAll(conn, ps);
        }
        return false;
    }

    /**
     * 分页查询
     *
     * @param sql
     * @param cls
     * @param page
     * @param limit
     * @param <T>
     * @return
     */
    public <T> PageInfo<T> selectPage(String sql, Class<T> cls, Integer page, Integer limit) {
        PageInfo<T> pageInfo = new PageInfo<>();
        int count = this.getCount(sql);
        sql = sql + "  limit " + ((page - 1) * limit) + "," + limit;
        //查询list
        List<T> date = this.selectList(sql, cls);
        pageInfo.setCount(count);
        pageInfo.setList(date);
        return pageInfo;
    }


    /**
     * 查询列表
     *
     * @param sql
     * @param cls
     * @param param
     * @param <T>
     * @return
     */
    public <T> List<T> selectList(String sql, Class<T> cls, Object... param) {
        Connection conn = JDBCUtil.getConnection();
        PreparedStatement ps = null;
        ResultSet rs = null;
        ArrayList<T> list = new ArrayList<>();
        try {
            ps = conn.prepareStatement(sql);
            for (int i = 0; i < param.length; i++) {
                ps.setObject(i + 1, param[i]);
            }
            rs = ps.executeQuery();
            List<T> bean = this.resultSetToBean(rs, cls);
            return bean;
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            JDBCUtil.closeAll(conn, ps, rs);
        }
        return list;
    }

    /**
     * 查询单个对象
     *
     * @param sql
     * @param cls
     * @param param
     * @param <T>
     * @return
     */
    public <T> T selectOne(String sql, Class<T> cls, Object... param) {
        List<T> list = this.selectList(sql, cls, param);
        if (list != null && list.size() == 1) {
            return list.get(0);
        }
        return null;
    }

    /**
     * 批量删除
     * @param sql
     * @param ids
     * @return
     */
    public boolean batchDelete(String sql,Object[] ids) {
        Connection conn = JDBCUtil.getConnection();
        PreparedStatement ps = null;
        try {
            conn.setAutoCommit(false);
            ps = conn.prepareStatement(sql);
            for (Object id : ids) {
                ps.setObject(1, id);
                ps.addBatch();
            }
            int[] count = ps.executeBatch();
            conn.commit();
            return count.length > 0 ? true : false;
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            JDBCUtil.closeAll(conn, ps);
        }
        return false;
    }

    /**
     * 获取数据的总条数
     *
     * @param sql
     * @return
     */
    private int getCount(String sql) {
        sql = "select count(1) from (" + sql + ") rs";
        Connection conn = JDBCUtil.getConnection();
        PreparedStatement ps = null;
        ResultSet rs = null;
        try {
            ps = conn.prepareStatement(sql);
            rs = ps.executeQuery();
            rs.next();
            return rs.getInt(1);
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return 0;
    }

    /**
     * 添加数据返回主键ID
     *
     * @param sql
     * @param par
     * @return
     */
    public int insert(String sql, Object... par) {
        Connection conn = JDBCUtil.getConnection();
        PreparedStatement ps = null;
        try {
            ps = conn.prepareStatement(sql, PreparedStatement.RETURN_GENERATED_KEYS);
            for (int i = 0; i < par.length; i++) {
                ps.setObject(1 + i, par[i]);
            }
            int m = ps.executeUpdate();
            ResultSet rs = ps.getGeneratedKeys();
            if (m > 0) {
                rs.next();
                return rs.getInt(1);
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
        return -1;
    }

    /**
     * 将ResultSet的数据转换为list
     *
     * @param rs  ResultSet数据集
     * @param cls 类名名称
     * @param <T>
     * @return
     */
    public <T> List<T> resultSetToBean(ResultSet rs, Class<T> cls) {
        ResultSetMetaData meta = null;
        ArrayList<T> list = new ArrayList<>();
        try {
            meta = rs.getMetaData();//获取rs里面的所有数据
            int columnCount = meta.getColumnCount();
            while (rs.next()) {
                T bean = cls.newInstance();//创建对象
                for (int i = 0; i < columnCount; i++) {
                    String columnName = meta.getColumnLabel(i + 1);//获取字段名
                    Object value = rs.getObject(columnName);//获取字段值
                    Field field = cls.getDeclaredField(columnName);//获取属性
                    field.setAccessible(true);//设置暴力破解
                    field.set(bean, value);//设置属性
                }
                list.add(bean);
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
        return list;
    }

}
